package com.homestay.dao.impl;

import com.homestay.beans.Books;
import com.homestay.beans.Order;
import com.homestay.dao.BooksDao;
import com.homestay.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

public class BooksDaoImpl implements BooksDao {

    QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());

    @Override
    public int add(Books books) {
        try {
            return qr.update("insert into tb_books values(null,?,?,?,null,now())", books.getVip_id(), books.getHome_id(), books.getEnterTime());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Order> query(String name, String card, String phone) {
        try {
            String sql = "select tb_books.id,tb_vip.id vipId,tb_home.id homeId,tb_vip.name,tb_vip.sex,tb_vip.card,tb_vip.phone,tb_home.num,tb_home.h_type,tb_books.enterTime,tb_books.exitTime from tb_books,tb_vip,tb_home " +
                    "where tb_books.vip_id=tb_vip.id and tb_books.home_id=tb_home.id and tb_vip.name like ? and tb_vip.card like ? and tb_vip.phone like ? order by tb_books.id desc";
            return qr.query(sql, new BeanListHandler<Order>(Order.class), "%" + name + "%", "%" + card + "%", "%" + phone + "%");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int delete(String id) {
        try {
            return qr.update("delete from tb_books where id=?", id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Order queryById(String id) {
        try {
            String sql = "select tb_books.id,tb_vip.id vipId,tb_home.id homeId,tb_vip.name,tb_vip.sex,tb_vip.card,tb_vip.phone,tb_home.num,tb_home.h_type,tb_books.enterTime,tb_books.exitTime from tb_books,tb_vip,tb_home " +
                    "where tb_books.vip_id=tb_vip.id and tb_books.home_id=tb_home.id and tb_books.id=?";
            return qr.query(sql, new BeanHandler<Order>(Order.class), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int update(Books books) {
        try {
            if(books.getExitTime()==null){
                return qr.update("update tb_books set vip_id=?,home_id=?,enterTime=? where id=?", books.getVip_id(), books.getHome_id(), books.getEnterTime(), books.getId());
            } else {
                return qr.update("update tb_books set vip_id=?,home_id=?,enterTime=?,exitTime=? where id=?", books.getVip_id(), books.getHome_id(), books.getEnterTime(), books.getExitTime(), books.getId());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int updateExitTime(String id) {
        try {
            return qr.update("update tb_books set exitTime=now() where id=?", id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Books queryBookById(String id) {
        try {
            return qr.query("select * from tb_books where id=?", new BeanHandler<Books>(Books.class), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Books queryBookByVipId(String id) {
        try {
            return qr.query("select * from tb_books where vip_id=?", new BeanHandler<Books>(Books.class), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}
